﻿-- ****************************************************************************************
--                                    STORED PROCEDURES
--
-- [sp_WebPlus_Markbook_InsertMarkbookAudit]      -- updated





SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_WebPlus_Markbook_InsertMarkbookAudit]
    @UserIDFK INT,
    @AcademicYear NVARCHAR(50),
    @FieldChanged NVARCHAR(50),
    @OldValue NVARCHAR(255),
    @NewValue NVARCHAR(255),
    @EnrolmentElementIDFK INT,
	@GroupID INT,
    @Created BIT,
    @SystemGenerated BIT,
	@resubmission BIT
AS
-- ============================================================================================
-- Author:      Chris Candeland
-- Create date: 04/06/2025
-- Description: SP to perform the Markbook Audit functionality
-- Modified:    27/10/2025 v1.40.2 Chris – UK date from UTC UKLocalTimeFromUtc, Forename and Surname empty string when null (i.e. not imported yet)
-- ============================================================================================
BEGIN
	DECLARE @Now DATETIME = dbo.UKLocalTimeFromUtc(GETUTCDATE());
    DECLARE @ElementTypeIDFK INT, @ResolvedStudentID INT, @ElementID int,@UserForename varchar(100),
	@UserSurname varchar(100),@ElementDesc varchar(512),@ElementTypeDesc varchar(50),@ElementTypeDescAlias varchar(20),@StudentForename varchar(100),
	@StudentSurname varchar(100),@StudentRef varchar(20),@CourseCode varchar(50),@FieldChangedAbbr varchar(8)= 'NotFound',@FieldChangedAlias varchar(50) = 'NotFound',@StudentIDFK int
	Declare @OGP_GroupCode varchar(50),@OGP_GroupTitle varchar(255)
	Declare @CourseID int;
	Declare @TopLevelElementID int;
	Declare @TopLevelElementCode varchar(20);
	Declare @ElementCode varchar(20);	

	SELECT TOP 1 @UserForename = u.Forenames,@UserSurname = u.Surname from [User] u where UserID = @UserIDFK
	SELECT TOP 1 @FieldChangedAlias = ISNULL(mf.OverrideName,mf.SystemName) , @FieldChangedAbbr =ISNULL(mf.OverrideAbbvn, mf.SystemAbbvn)
	FROM [dbo].[OGP_MarkbookField] mf
	WHERE [SystemName] = @FieldChanged
	
	SELECT top 1 @OGP_GroupCode = [OGP_GroupCode], @OGP_GroupTitle = [OGP_GroupTitle]
	FROM [dbo].[OGP_Group] 
	where id =@GroupID
	
	SELECT TOP 1
		@ElementTypeIDFK = et.ID,
		@ElementDesc = e.[description],
		@ElementTypeDesc = et.[Description],
		@ElementTypeDescAlias = 
			CASE 
				WHEN ISNULL(LTRIM(RTRIM(et.ElementAlternativeName)), '') = '' THEN et.[Description]
				ELSE et.ElementAlternativeName
			END,
		@ResolvedStudentID = oe.OGP_StudentID,
		@StudentForename = ISNULL(NULLIF(LTRIM(RTRIM(s.Forenames)), ''), N''),-- s.Forenames,
		@StudentSurname = ISNULL(NULLIF(LTRIM(RTRIM(s.Surname)), ''), N''),-- s.Surname,
		@StudentRef = s.StudentRef,
		@StudentIDFK = s.ID,
		@CourseCode = c.CourseCode,
		@ElementID = e.ID,
		@TopLevelElementID = e.TopLevelParentID,
		@ElementCode = e.ElementCode,
		@TopLevelElementCode = topLevel.ElementCode,
		@CourseID = c.ID
    FROM dbo.OGP_Element e
    INNER JOIN dbo.OGP_ElementType et ON e.OGP_ElementTypeID = et.ID
    INNER JOIN dbo.OGP_EnrolmentElement oge ON e.ID = oge.OGP_ElementID
    INNER JOIN dbo.OGP_Enrolment oe ON oe.ID = oge.OGP_EnrolmentID
    INNER JOIN dbo.OGP_Student s ON s.ID = oe.OGP_StudentID
    INNER JOIN Course c ON c.id = oe.CourseID
	LEFT JOIN OGP_Element topLevel ON topLevel.TopLevelParentID = e.TopLevelParentID --is null when element is top row
    WHERE oge.ID = @EnrolmentElementIDFK;

    INSERT INTO dbo.OGP_MarkbookAudit (
		[UserIDFK]
		,[UserForename]
		,[UserSurname]
		,[DateChanged]
		,[AcademicYear]
		,[FieldChanged]
		,[OldValue]
		,[NewValue]
		,[ElementDesc]
		,[ElementIDFK]
		,[ElementTypeIDFK]
		,[ElementTypeDesc]
		,[StudentForename]
		,[StudentSurname]
		,[StudentRef]
		,[OGP_StudentIDFK]
		,[CourseCode]
		,[GroupIDFK]
		,[EnrolmentElementIDFK]
		,[Created]
		,[FieldChangedAbbr]
		,[FieldChangedAlias]
		,[StudentIDFK]
		,[ElementTypeDescAlias]
		,[SystemGenerated]
		,[Resubmission]
		,[GroupCode]
		,[GroupTitle]
		,[TopLevelElementIDFK]
		,[CourseIDFK]
		,[ElementCode]
		,TopLevelElementCode
    )
    VALUES (
			@UserIDFK,
			@UserForename,
			@UserSurname,
			@Now,
			@AcademicYear,
			@FieldChanged,
			@OldValue,
			@NewValue,
			@ElementDesc,
			@ElementID,
			@ElementTypeIDFK,
			@ElementTypeDesc,
			@StudentForename,
			@StudentSurname,
			@StudentRef,
			@ResolvedStudentID,
			@CourseCode,
			@GroupID,
			@EnrolmentElementIDFK,
			@Created,
			@FieldChangedAbbr,
			@FieldChangedAlias,
			@StudentIDFK,
			@ElementTypeDescAlias,
			@SystemGenerated,
			@resubmission,
			@OGP_GroupCode,
			@OGP_GroupTitle,
			@TopLevelElementID,
			@CourseID,
			@ElementCode,
			@TopLevelElementCode
    );
END;
